﻿using LCL;
using LCL.CodeEngine;
using LCL.Config;
using LCL.LData;
using LCL.Tools.Build;
using System;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Windows.Forms;
using ICSharpCode.TextEditor;
using ICSharpCode.TextEditor.Document;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;

namespace LCL.Tools.WinFrm.Common {
    public partial class FrmSqlBuildEditor:Form {
        private TextEditorControl txtSQL = null;
        private TextEditorControl txtResult = null;
        private DbConfigInfo config = null;
        public DbAccesser dba = null;
        public FrmSqlBuildEditor() {
            InitializeComponent();
            config = DbConfig.GetConfig();
            dba = Utility.GetDba();
            UIFactory.UIFact().BindDataBaseList(this.dblist);
            txtSQL = new TextEditorControl();
            txtSQL.ContextMenuStrip = this.sqlMenu;
            txtSQL.Dock = DockStyle.Fill;
            txtSQL.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            txtSQL.Encoding = Encoding.Default;
            txtSQL.IsIconBarVisible = false;
            txtSQL.ShowInvalidLines = false;
            txtSQL.ShowSpaces = false;
            txtSQL.ShowTabs = false;
            txtSQL.ShowEOLMarkers = false;
            txtSQL.ShowVRuler = false;
            txtSQL.Font = new System.Drawing.Font("新宋体",9f);

            txtResult = new TextEditorControl();
            txtResult.ContextMenuStrip = this.resultMenu;
            txtResult.Dock = DockStyle.Fill;
            txtResult.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            txtResult.Encoding = Encoding.Default;
            txtResult.IsIconBarVisible = false;
            txtResult.ShowInvalidLines = false;
            txtResult.ShowSpaces = false;
            txtResult.ShowTabs = false;
            txtResult.ShowEOLMarkers = false;
            txtResult.ShowVRuler = false;
            txtResult.Font = new System.Drawing.Font("新宋体",9f);

            this.groupBox1.Controls.Add(txtSQL);
            this.groupBox2.Controls.Add(txtResult);
        }

        private void 生成INSERTINTOToolStripMenuItem_Click(object sender,EventArgs e) {
            try {
                string sql = $"use [{AppConfig.GetInstance().DbName}]  " + txtSQL.Text;
                var pattern = @"from\s*(.*?)\s*where";
                var result = Regex.Match(sql,pattern).Groups[1].Value;
                var table = dba.ExecuteDataTable(sql);
                table.TableName = result;
                txtResult.Text = GenerateInsertSql(table);
            } catch(Exception ex) {
                UIFactory.ShowThreadExceptionDialog(ex);
            }
        }

        /// <summary>
        /// 请设置dt.TableName
        /// 根据DataTable生成sql数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string GenerateInsertSql(DataTable dt) {
            StringBuilder strSql = new StringBuilder();
            StringBuilder builder = new StringBuilder();
            builder.AppendLine($"SET IDENTITY_INSERT [{dt.TableName}] ON");
            if(dt != null && dt.Rows.Count > 0) {
                string cols = string.Join(",",dt.Columns.Cast<DataColumn>().Select(x => $"[{x.ColumnName}]").ToArray());
                foreach(DataRow dr in dt.Rows) {
                    var vals = string.Join(",",dr.ItemArray.Select(x => {
                        if(DBNull.Value.GetType() == x.GetType()) {
                            return "null";
                        } else {
                            return $"'{x}'";
                        }
                    }).ToArray());

                    builder.AppendLine($"insert into {dt.TableName} ({cols}) values({vals})");
                }
            }
            builder.AppendLine($"SET IDENTITY_INSERT [{dt.TableName}] OFF");
            return builder.ToString();
        }

        private void dblist_TextChanged(object sender,EventArgs e) {
            if(dblist.SelectedItem == null) return;
            AppConfig.GetInstance().DbName = dblist.SelectedItem.ToString();
        }

        private void 复制ToolStripMenuItem_Click(object sender,EventArgs e) {
            string selectedText = this.txtResult.ActiveTextAreaControl.SelectionManager.SelectedText;
            Clipboard.SetDataObject(selectedText);
        }
    }
}
